package indi.DAO;

import indi.entity.Manager;
import indi.entity.RecordOfBorrow;
import indi.entity.Title;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Dao {
    static String driverName = "com.mysql.jdbc.Driver";
    static String url = "jdbc:mysql://localhost:3306/library?characterEncoding=UTF-8";
    static String user = "root";
    static String password = "23451";
    static Connection connection;
    static boolean connected;

    //连接到数据库
    public void connectToDatabase(){
        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url, user, password);
            connected=true;
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //抽象过程：添加、删除
    public boolean update(String sql){
        try {
            if (connected == true) {
                Statement statement = connection.createStatement();
                int count = statement.executeUpdate(sql);
                if (count > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //抽象过程：添加并返回自动生成的ID
    public String updateAndReturn(String insertSql,String getIDSql){
        String result=null;
        try {
            if (connected == true) {
                if(update(insertSql)){
                    Statement statement = connection.createStatement();
                    ResultSet rs=statement.executeQuery(getIDSql);
                    while(rs.next()){
                        result=String.valueOf(rs.getInt("ID"));
                    }
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //抽象过程：查询并返回结果集
    public ResultSet query(String sql){
        ResultSet rs=null;
        try {
            if (connected == true) {
                Statement statement = connection.createStatement();
                rs = statement.executeQuery(sql);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return rs;
    }

    //存储生成的借阅证信息
    public String registerCard(String insertSql,String getIDSql){
        return updateAndReturn(insertSql,getIDSql);
    }

    //删除借阅证
    public boolean deleteCard(String sql){
        return update(sql);
    }

    //添加管理员
    public String addManager(String sql1,String sql2){
        update(sql1);
        String result=null;
        ResultSet rs=query(sql2);
        try {
            while (rs.next()) {
                result = rs.getString(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //存储借阅杂志的信息
    public String addBorrowRecord(String insertsql,String idsql) {
        return  updateAndReturn(insertsql,idsql);
    }

    //向借阅证中添加借阅信息
    public boolean addCardRecord(String sql){
        return update(sql);
    }

    //删除借阅信息
    public boolean deleteBorrowRecord(String sql){
        return update(sql);
    }

    //根据ISBN和借阅证号查询一条借阅信息
    public RecordOfBorrow queryBorrowRecord(String sql){
        RecordOfBorrow record=null;
        ResultSet rs=query(sql);
        try {
            while (rs.next()) {
                String ID = String.valueOf(rs.getInt(1));
                int isreturn = rs.getInt(2);
                Date borrowTime = rs.getTimestamp(3);
                Date dueTime = rs.getTimestamp(4);
                String cardID = String.valueOf(rs.getInt(5));
                String ISBN = rs.getString(6);
                Date returnTime=rs.getTimestamp(7);
                record = new RecordOfBorrow(ID, isreturn, borrowTime, dueTime, cardID, ISBN,returnTime);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return record;
    }

    //根据ISBN查询是图书还是杂志
    public String checkType(String sql){
        String type=null;
        ResultSet rs=query(sql);
        try {
            while (rs.next()) {
                type = rs.getString("type");
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return type;
    }

    //查询借阅证已借的图书数
    public int queryBorrowedNum(String sql){
        int num=0;
        ResultSet rs=query(sql);
        try {
            while (rs.next()) {
                num = rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    //借书时将图书的借阅次数加1，已借出数加1
    public boolean addNum(String sql){
        return update(sql);
    }

    //添加书籍
    public boolean addBook(String sql){
        return update(sql);
    }

    //查找书籍
    public Title getTitle(String sql){
        Title t=null;
        ResultSet rs= query(sql);
        try {
            while (rs.next()) {
                t = new Title(rs.getString("ISBN"), rs.getString("author"), rs.getInt("totalNum"), rs.getString("bookname"), rs.getFloat("price"), rs.getString("type"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return t;
    }

    //还书时查看是否延期
    public Date overdue(String sql) {
        SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
        Date dueTime=null;
        ResultSet rs=query(sql);
        try {
            while (rs.next()) {
                dueTime = rs.getTimestamp(1);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return dueTime;
    }

    //还书时添加还书时间
    public boolean addReturnTime(String sql){
        return update(sql);
    }

    //删除卡中的一项借阅记录
    public boolean deleteRecord(String sql){
        return update(sql);
    }

    //获取该种图书所能借阅的时长
    public int queryPeriod(String sql){
        ResultSet rs=query(sql);
        int result=0;
        try {
            while (rs.next()) {
                result = rs.getInt("period");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //查询该类学生最多能借的图书数
    public int queryMaxBorrowNum(String sql){
        ResultSet rs=query(sql);
        int result=0;
        try {
            while (rs.next()) {
                result = rs.getInt("number");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //查询该借阅证是否在数据库
    public boolean checkID(String sql){
        ResultSet rs=query(sql);
        int num=0;
        boolean result=false;
        try {
            if (rs.next()) {
                num = rs.getInt(1);
                if(num>0) result=true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //查询所有借阅信息
    public List<RecordOfBorrow> queryAllRecords(String sql){
        ResultSet rs=query(sql);
        List<RecordOfBorrow> list=new ArrayList<>();
        RecordOfBorrow record;
        boolean result=false;
        try {
            while (rs.next()) {
                record=new RecordOfBorrow(String.valueOf(rs.getInt("ID")),rs.getInt("isReturn"),rs.getTimestamp("borrowTime"),rs.getTimestamp("dueTime"),String.valueOf(rs.getInt("cardID")),rs.getString("ISBN"),rs.getTimestamp("returnTime"));
                list.add(record);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public float queryFine(String sql){
        ResultSet rs=query(sql);
        float result=0;
        try {
            while (rs.next()) {
                result = rs.getFloat("fine");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }
}
